{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "🔗 数据库连接配置完成\n"
     ]
    }
   ],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import pymysql\n",
    "from sqlalchemy import create_engine, text\n",
    "from IPython.display import display\n",
    "\n",
    "# 数据库连接配置\n",
    "DB_CONFIG = {\n",
    "    'host': 'host.docker.internal',\n",
    "    'port': 9030,\n",
    "    'user': 'root',\n",
    "    'password': 'pass',\n",
    "    'charset': 'utf8mb4'\n",
    "}\n",
    "\n",
    "print(\"🔗 数据库连接配置完成\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 数据库连接成功\n"
     ]
    }
   ],
   "source": [
    "# 创建数据库连接\n",
    "def create_connection():\n",
    "    try:\n",
    "        connection_string = f\"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/demo\"\n",
    "        engine = create_engine(connection_string, echo=False)\n",
    "        \n",
    "        # 测试连接\n",
    "        with engine.connect() as conn:\n",
    "            result = conn.execute(text(\"SELECT 1\"))\n",
    "            print(\"✅ 数据库连接成功\")\n",
    "        \n",
    "        return engine\n",
    "    except Exception as e:\n",
    "        print(f\"❌ 连接失败: {e}\")\n",
    "        return None\n",
    "\n",
    "# 建立连接\n",
    "engine = create_connection()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 数据插入成功\n",
      "📊 影响行数: 1\n"
     ]
    }
   ],
   "source": [
    "# 执行 INSERT 语句\n",
    "if engine:\n",
    "    try:\n",
    "        # 插入语句\n",
    "        insert_sql = \"\"\"\n",
    "        INSERT INTO production_fault_records (\n",
    "            record_id, \n",
    "            record_time, \n",
    "            recorder, \n",
    "            production_line, \n",
    "            batch, \n",
    "            location, \n",
    "            fault, \n",
    "            fault_level, \n",
    "            status\n",
    "        ) VALUES (\n",
    "            1001,\n",
    "            '2025-01-15 10:30:00',\n",
    "            '张三',\n",
    "            '生产线A',\n",
    "            'BATCH_20250115_001',\n",
    "            '工位3-机械臂',\n",
    "            '机械臂定位精度偏差，影响产品质量',\n",
    "            3,\n",
    "            0\n",
    "        );\n",
    "        \"\"\"\n",
    "        \n",
    "        # 执行插入\n",
    "        with engine.connect() as conn:\n",
    "            result = conn.execute(text(insert_sql))\n",
    "            conn.commit()\n",
    "            print(\"✅ 数据插入成功\")\n",
    "            print(f\"📊 影响行数: {result.rowcount}\")\n",
    "            \n",
    "    except Exception as e:\n",
    "        print(f\"❌ 插入失败: {e}\")\n",
    "else:\n",
    "    print(\"❌ 无法执行插入：数据库连接失败\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "🔍 插入的数据:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>record_id</th>\n",
       "      <th>record_time</th>\n",
       "      <th>recorder</th>\n",
       "      <th>production_line</th>\n",
       "      <th>batch</th>\n",
       "      <th>location</th>\n",
       "      <th>fault</th>\n",
       "      <th>fault_level</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2025-01-15 10:30:00</td>\n",
       "      <td>张三</td>\n",
       "      <td>生产线A</td>\n",
       "      <td>BATCH_20250115_001</td>\n",
       "      <td>工位3-机械臂</td>\n",
       "      <td>机械臂定位精度偏差，影响产品质量</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1001</td>\n",
       "      <td>2025-01-15 10:30:00</td>\n",
       "      <td>张三</td>\n",
       "      <td>生产线A</td>\n",
       "      <td>BATCH_20250115_001</td>\n",
       "      <td>工位3-机械臂</td>\n",
       "      <td>机械臂定位精度偏差，影响产品质量</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   record_id         record_time recorder production_line               batch  \\\n",
       "0       1001 2025-01-15 10:30:00       张三            生产线A  BATCH_20250115_001   \n",
       "1       1001 2025-01-15 10:30:00       张三            生产线A  BATCH_20250115_001   \n",
       "\n",
       "  location             fault  fault_level  status  \n",
       "0  工位3-机械臂  机械臂定位精度偏差，影响产品质量            3       0  \n",
       "1  工位3-机械臂  机械臂定位精度偏差，影响产品质量            3       0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "📊 表中总记录数: 2\n"
     ]
    }
   ],
   "source": [
    "# 验证插入结果\n",
    "if engine:\n",
    "    try:\n",
    "        # 查询刚插入的数据\n",
    "        query_sql = \"SELECT * FROM production_fault_records WHERE record_id = 1001;\"\n",
    "        \n",
    "        result_df = pd.read_sql(query_sql, engine)\n",
    "        \n",
    "        if not result_df.empty:\n",
    "            print(\"🔍 插入的数据:\")\n",
    "            display(result_df)\n",
    "        else:\n",
    "            print(\"⚠️ 未找到插入的数据\")\n",
    "            \n",
    "        # 查看表中总记录数\n",
    "        count_sql = \"SELECT COUNT(*) as total_records FROM production_fault_records;\"\n",
    "        count_df = pd.read_sql(count_sql, engine)\n",
    "        print(f\"\\n📊 表中总记录数: {count_df['total_records'].iloc[0]}\")\n",
    "        \n",
    "    except Exception as e:\n",
    "        print(f\"❌ 查询失败: {e}\")\n",
    "else:\n",
    "    print(\"❌ 无法查询：数据库连接失败\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 批量数据插入成功\n",
      "📊 新增记录数: 3\n"
     ]
    }
   ],
   "source": [
    "# 插入更多数据示例\n",
    "if engine:\n",
    "    try:\n",
    "        # 插入多条记录\n",
    "        insert_multiple_sql = \"\"\"\n",
    "        INSERT INTO production_fault_records (\n",
    "            record_id, record_time, recorder, production_line, batch, \n",
    "            location, fault, fault_level, status\n",
    "        ) VALUES \n",
    "        (1002, '2025-01-15 11:00:00', '李四', '生产线B', 'BATCH_20250115_002', \n",
    "         '工位1-输送带', '输送带速度异常', 2, 1),\n",
    "        (1003, '2025-01-15 11:30:00', '王五', '生产线A', 'BATCH_20250115_003', \n",
    "         '工位5-检测设备', '传感器读数错误', 1, 0),\n",
    "        (1004, '2025-01-15 12:00:00', '赵六', '生产线C', 'BATCH_20250115_004', \n",
    "         '工位2-焊接机', '焊接温度过高', 4, 2);\n",
    "        \"\"\"\n",
    "        \n",
    "        with engine.connect() as conn:\n",
    "            result = conn.execute(text(insert_multiple_sql))\n",
    "            conn.commit()\n",
    "            print(\"✅ 批量数据插入成功\")\n",
    "            print(f\"📊 新增记录数: {result.rowcount}\")\n",
    "            \n",
    "    except Exception as e:\n",
    "        print(f\"❌ 批量插入失败: {e}\")\n",
    "else:\n",
    "    print(\"❌ 无法执行插入：数据库连接失败\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "📋 表中所有数据 (共 5 条记录):\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>record_id</th>\n",
       "      <th>record_time</th>\n",
       "      <th>recorder</th>\n",
       "      <th>production_line</th>\n",
       "      <th>batch</th>\n",
       "      <th>location</th>\n",
       "      <th>fault</th>\n",
       "      <th>fault_level</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2025-01-15 10:30:00</td>\n",
       "      <td>张三</td>\n",
       "      <td>生产线A</td>\n",
       "      <td>BATCH_20250115_001</td>\n",
       "      <td>工位3-机械臂</td>\n",
       "      <td>机械臂定位精度偏差，影响产品质量</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1001</td>\n",
       "      <td>2025-01-15 10:30:00</td>\n",
       "      <td>张三</td>\n",
       "      <td>生产线A</td>\n",
       "      <td>BATCH_20250115_001</td>\n",
       "      <td>工位3-机械臂</td>\n",
       "      <td>机械臂定位精度偏差，影响产品质量</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1002</td>\n",
       "      <td>2025-01-15 11:00:00</td>\n",
       "      <td>李四</td>\n",
       "      <td>生产线B</td>\n",
       "      <td>BATCH_20250115_002</td>\n",
       "      <td>工位1-输送带</td>\n",
       "      <td>输送带速度异常</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>2025-01-15 11:30:00</td>\n",
       "      <td>王五</td>\n",
       "      <td>生产线A</td>\n",
       "      <td>BATCH_20250115_003</td>\n",
       "      <td>工位5-检测设备</td>\n",
       "      <td>传感器读数错误</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1004</td>\n",
       "      <td>2025-01-15 12:00:00</td>\n",
       "      <td>赵六</td>\n",
       "      <td>生产线C</td>\n",
       "      <td>BATCH_20250115_004</td>\n",
       "      <td>工位2-焊接机</td>\n",
       "      <td>焊接温度过高</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   record_id         record_time recorder production_line               batch  \\\n",
       "0       1001 2025-01-15 10:30:00       张三            生产线A  BATCH_20250115_001   \n",
       "1       1001 2025-01-15 10:30:00       张三            生产线A  BATCH_20250115_001   \n",
       "2       1002 2025-01-15 11:00:00       李四            生产线B  BATCH_20250115_002   \n",
       "3       1003 2025-01-15 11:30:00       王五            生产线A  BATCH_20250115_003   \n",
       "4       1004 2025-01-15 12:00:00       赵六            生产线C  BATCH_20250115_004   \n",
       "\n",
       "   location             fault  fault_level  status  \n",
       "0   工位3-机械臂  机械臂定位精度偏差，影响产品质量            3       0  \n",
       "1   工位3-机械臂  机械臂定位精度偏差，影响产品质量            3       0  \n",
       "2   工位1-输送带           输送带速度异常            2       1  \n",
       "3  工位5-检测设备           传感器读数错误            1       0  \n",
       "4   工位2-焊接机            焊接温度过高            4       2  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "🎉 演示完成！\n"
     ]
    }
   ],
   "source": [
    "# 查看所有插入的数据\n",
    "if engine:\n",
    "    try:\n",
    "        # 查询所有数据\n",
    "        all_data_sql = \"SELECT * FROM production_fault_records ORDER BY record_time;\"\n",
    "        \n",
    "        all_df = pd.read_sql(all_data_sql, engine)\n",
    "        \n",
    "        print(f\"📋 表中所有数据 (共 {len(all_df)} 条记录):\")\n",
    "        display(all_df)\n",
    "        \n",
    "    except Exception as e:\n",
    "        print(f\"❌ 查询失败: {e}\")\n",
    "else:\n",
    "    print(\"❌ 无法查询：数据库连接失败\")\n",
    "\n",
    "print(\"\\n🎉 演示完成！\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
